package com.iamteer.day17;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

import com.iamteer.util.JdbcUtil;

public class PreparedStatementEg {
	//PreparedStatement 执行 DDL——增加
	@Test
	public void testInsert() {
		PreparedStatement stmt = null;
		Connection conn = null;

		try {
			//1 获取连接对象
			conn = JdbcUtil.getConnection();

			//2 准备预编译的 sql
			String sql = "INSERT INTO student(name,gender) VALUES(?,?)";

			//3 创建 PreparedStatement
			stmt = conn.prepareStatement(sql);

			//4 设置参数值
			stmt.setString(1,"李四");
			stmt.setString(2,"男");
			
			//5 发送 sql 语句，执行 sql 语句，返回结果
			int count = stmt.executeUpdate();
			System.out.println("Affected " + count + " lines");

		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt);
		}
	}
	
	//PreparedStatement 执行 DDL——更新
	@Test
	public void testUpdate() {
		PreparedStatement stmt = null;
		Connection conn = null;
		try {
			//1 获取连接
			conn = JdbcUtil.getConnection();
			
			//2 准备预编译的 sql
			String sql = "Update student SET gender=? where id=?";
			
			//3 创建 PreparedStatement
			stmt = conn.prepareStatement(sql);
			
			//4 设置参数值
			stmt.setString(1, "女");
			stmt.setInt(2, 1);
			
			//5 发送 sql 语句，执行 sql 语句，返回结果
			int count = stmt.executeUpdate();
			System.out.println("Affected " + count + " lines");
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt);
		}
	}
	
	//PreparedStatement 执行 DDL——删除
	@Test
	public void testDelete() {
		PreparedStatement stmt = null;
		Connection conn = null;
		try {
			//1 获取连接
			conn = JdbcUtil.getConnection();
			
			//2 准备预编译的 sql
			String sql = "DELETE FROM student WHERE id=?";
			
			//3 创建 PreparedStatement
			stmt = conn.prepareStatement(sql);
			
			//4 设置参数值
			stmt.setInt(1, 1);
			
			//5 发送 sql 语句，执行 sql 语句，返回结果
			int count = stmt.executeUpdate();
			System.out.println("Affected " + count + " lines");
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt);
		}
	}
	
	//PreparedStatement 执行 DDL——查询
	@Test
	public void testQuery() {
		PreparedStatement stmt = null;
		Connection conn = null;
		ResultSet rs = null;
		try {
			//1 获取连接
			conn = JdbcUtil.getConnection();
			
			//2 准备预编译的 sql
			String sql = "SELECT * FROM student";
			
			//3 创建 PreparedStatement
			stmt = conn.prepareStatement(sql);
			
			//4 发送 sql 语句，执行 sql 语句，返回结果
			rs = stmt.executeQuery();
			
			//5 遍历结果
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String gender = rs.getString("gender");
				System.out.println(id + " " + name + " " + gender);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt,rs);
		}
	}
	
	//SQL注入：Statement 无能为力
	@Test
	public void testInjection1() {
		Statement stmt = null;
		Connection conn = null;
		ResultSet rs = null;
		
		//只要注入：' OR 1=1 -- ，无论任何账户都可以成功了。
		String username = "chucjlljljljlk' OR 1=1 -- ";	
		String password = "1111fdafdafda11";
		try {
			//1 获取连接
			conn = JdbcUtil.getConnection();
			
			//2 准备 sql
			String sql = "SELECT * FROM users WHERE username='" + username +"' AND password='" + password + "'";
			
			//3 创建 PreparedStatement
			stmt = conn.createStatement();
			
			//4 发送 sql 语句，执行 sql 语句，返回结果
			rs = stmt.executeQuery(sql);
			
			//5 遍历结果
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("username");
				String gender = rs.getString("password");
				System.out.println(id + " " + name + " " + gender);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt,rs);
		}
	}
	
	//SQL注入：PreparedStatement 可以防止
	@Test
	public void testInjection2() {
		PreparedStatement stmt = null;
		Connection conn = null;
		ResultSet rs = null;
		String username = "chucjlljljljlk' OR 1=1 -- ";	
		String password = "1111fdafdafda11";
		try {
			//1 获取连接
			conn = JdbcUtil.getConnection();
			
			//2 准备预编译的 sql
			String sql = "SELECT * FROM users WHERE username=? AND password=?";
			
			//3 创建 PreparedStatement
			stmt = conn.prepareStatement(sql);
			
			//4 设置参数
			stmt.setString(1, username);
			stmt.setString(2, password);
			
			//5 发送 sql 语句，执行 sql 语句，返回结果
			rs = stmt.executeQuery();
			
			//6 遍历结果
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("username");
				String gender = rs.getString("password");
				System.out.println(id + " " + name + " " + gender);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.close(conn, stmt,rs);
		}
	}
}
